package cc.rengu.redp.bizimpl.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelOperaUtil {

    public String getRange(int offset, int rowId, int colCount) {
        char start = (char)('A' + offset);
        if (colCount <= 25) {
            char end = (char)(start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char)('A' + 25);
                } else {
                    endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char)('A' + 25);
                    endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char)(endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

    public static HSSFDataValidation setValidate(short firstRow, short lastRow, short firstCol, short lastCol, String[] dateFormat) {
        // 创建一个规则
        DVConstraint constraint = DVConstraint.createNumericConstraint( Integer.parseInt(dateFormat[1]),Integer.parseInt(dateFormat[2]),
                dateFormat[3], dateFormat[4]);
        // 设定在哪个单元格生效
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 创建规则对象
        HSSFDataValidation ret = new HSSFDataValidation(regions, constraint);
        ret.createPromptBox("输入提示",dateFormat[5]);
        return ret;
    }

    public void setCellType(short firstRow, short lastRow, short col, HSSFSheet sheet,
                            HSSFCellStyle cellStyle) {
        for(int r=firstRow;r<lastRow;r++){
            HSSFRow row = sheet.getRow(r);
            if(row == null)
                row = sheet.createRow(r);
            HSSFCell cell =  row.getCell(col);
            if(cell == null){
                cell = row.createCell(col);
            }
            cell.setCellStyle(cellStyle);
        }
    }

    public static HSSFDataValidation setCheckBoxs(short firstRow, short lastRow, short firstCol, short lastCol,String indirect,String dataFormat) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(indirect);

        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", dataFormat);
        dataValidation.setShowPromptBox(true);

        return dataValidation;
    }

    public static HSSFDataValidation setCheckBoxs(short firstRow, short lastRow, short firstCol, short lastCol,String[] boxVals,String dataFormat) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(boxVals);

        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", dataFormat);
        dataValidation.setShowPromptBox(true);

        return dataValidation;
    }
}
